//
//  PYFMDB.m
//  Operations library of sqlite base on FMDB
//
//  Created by terry on 15/3/28.
//  Copyright (c) 2015年 Velda. All rights reserved.
//

#import "MHFMDB.h"
#import "FMDB.h"
#import "MHFMDBHeader.h"


@implementation MHFMDB
#pragma mark - 创建数据库连接
+(instancetype)dbWithDbName:(NSString *)dbName
{
    return [[self alloc] initWithDbName:dbName];
}

-(instancetype)initWithDbName:(NSString *)dbName
{
    if(self =[super init]){
        [self setDbName:dbName];
        [self createCacheTable];
    }
    return self;
}

#pragma mark - 创建缓存数据库表
-(BOOL)createCacheTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS `_cache` (cacheID text primary key,cacheData BLOB);";
    return [self excuteUpdateWithSql:sql];
}

#pragma mark -创建数据库表
- (bool)createTableWithDict:(NSDictionary *)dict tableName:(NSString *)tableName
{
    NSString *currentTableName = [NSString stringWithFormat:@"%@%@",self.prefix,tableName];
    __block NSMutableString *sql = [NSMutableString stringWithFormat:@"CREATE TABLE IF NOT EXISTS `%@` (",currentTableName];
    [dict enumerateKeysAndObjectsUsingBlock:^(id key, id obj, BOOL *stop) {
        sql = [NSMutableString stringWithFormat:@"%@ %@ %@,",sql,key,obj];
    }];
    //去除右侧多余 ','
    NSString *leftsql = [sql stringByTrimmingCharactersInSet:[NSCharacterSet characterSetWithCharactersInString:@","]];
    sql = [NSMutableString stringWithFormat:@"%@);",leftsql];
    _lastSql = sql;
    return [self excuteUpdateWithSql:sql];
}


#pragma mark - 执行数据库查询与更新
- (NSArray *)excuteQueryWithSql:(NSString *)sql
{
    __block NSArray *result = [NSArray array];
    [_queue inDatabase:^(FMDatabase *db) {
        NSMutableArray *arr = [NSMutableArray array];
        FMResultSet *rs = [db executeQuery:sql];
        while ([rs next]) {
            NSMutableDictionary *dict = [NSMutableDictionary dictionary];
            NSMutableString *key = [NSMutableString string];
            for (int i=0; i<_fields.count; i++) {
                key = [NSMutableString stringWithString:[_fields objectAtIndex:i]];
                //过滤"xxx as yyy" 情况
                NSArray *keyarr =[key componentsSeparatedByString:@" as "];
                if ([keyarr count]>0) {
                    key =[NSMutableString stringWithString: [[keyarr lastObject] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]]];
                }
                //找到对应的currentTableField的type;keytype 只区分bloc和非bloc,非bloc一律转成nsstring
                NSMutableString *keytype = nil;
                //当key=*时则遍历_fields
                if ([key isEqualToString:@"*"]) {
                    for (int j=0; j<_currentTableFields.count; j++) {
                        key = [NSMutableString stringWithString:[_currentTableFields.allKeys objectAtIndex:j]];
                        keytype = [NSMutableString stringWithString:[_currentTableFields.allValues objectAtIndex:j]];
                        [keytype isEqualToString:@"bolb"] ? [dict setObject:[rs dataForColumn:key] forKey:key]:[dict setObject:[rs stringForColumn:key] forKey:key];
                    }
                }
                else{
                    //其他情况则分析key是否在currentTableFields.allKeys中是否存在
                    keytype = [_currentTableFields.allKeys containsObject:key] ?  _currentTableFields[key]:nil;
//                    if ([rs dataForColumn:key]) {
                        [keytype isEqualToString:@"bolb"] ? [dict setObject:[rs dataForColumn:key] forKey:key]:[dict setObject:[rs stringForColumn:key] forKey:key];
//                    }
                }
            }
            [arr addObject:dict];
        }
        result  = [NSArray arrayWithArray:arr];
    }];
    //记录到最后sql
    _lastSql = sql;
    if(!result){
        NSLog(@"MHFMDB QUERY Empty: %@",sql);
    }
    return result;
}

/**
 *  执行sql更新
 */
- (bool)excuteUpdateWithSql:(NSString *)sql
{
    __block bool result;
    [_queue inDatabase:^(FMDatabase *db) {
        result = [db executeUpdate:sql];
    }];
    //记录到最后sql
    _lastSql = sql;
    //如果执行失败打印到前台
    if(!result){
        NSLog(@"MHFMDB UPDATE Failed: %@",sql);
    }
    return result;
}


#pragma mark - field 字段设置
- (instancetype)fieldsWithArray:(NSArray *)arr
{
    _fields = arr;
    return self;
}

/**
 *  从字符串中设置要查询的字段信息
 */
- (instancetype)fieldsWithString:(NSString *)str
{
    //将字符串转为数组
    NSArray *fields = [str componentsSeparatedByString:@","];
    if ([fields count]==0) {
        _fields = [NSArray arrayWithObject:str];
        return self;
    }
    __block NSMutableArray *result=[NSMutableArray array];
    [fields enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop) {
        NSString *trimobj = [obj stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]];
        [result addObject:trimobj];
    }];
    _fields = [NSArray arrayWithArray:result];
    return self;
}
#pragma mark - where条件设置
- (instancetype)whereWithString:(NSString *)str
{
    self.where = str;
    return self;
}
/**
 *  从字典中设置要查询的where条件
 */
- (instancetype)whereWithDict:(NSDictionary *)dict
{
    NSMutableString *mutablestr = [NSMutableString string];
    NSArray *allkeys = [dict allKeys];
    NSMutableString *tmp = [NSMutableString string];
    for (int i=0; i<allkeys.count; i++) {
        id key = [allkeys objectAtIndex:i];
        id obj = [dict objectForKey:key];
        //value=字符串则直接等号赋值
        if ([obj isKindOfClass:[NSString class]]) {
            tmp = [NSMutableString stringWithFormat:@" `%@` = '%@' AND ",key,obj];
        }
        //value=数组则提取赋值符合
        if ([obj isKindOfClass:[NSArray class]] ) {
            tmp = [NSMutableString stringWithFormat:@" `%@` %@ '%@' AND ",key,[obj objectAtIndex:0],[obj objectAtIndex:1]];
        }
        mutablestr = [NSMutableString stringWithFormat:@"%@%@",mutablestr,tmp];
    }
    self.where = [NSString stringWithFormat:@"%@ 1",mutablestr];
    return self;
}

#pragma mark - limit 条件设置
- (instancetype)limitWithString:(NSString *)str
{
    if ([str containsString:@","]) {
        self.limit = str;
    }
    else{
        self.limit = [NSString stringWithFormat:@"0,%@",str];
    }
    return self;
}
/**
 *  从数组中设置limit条件
 */
-(instancetype)limitWithArray:(NSArray *)arr
{
    if ([arr count]>1) {
        self.limit = [arr componentsJoinedByString:@","];
    } else {
        self.limit = [arr lastObject];
    }
    return self;
}
/**
 *  从start 到 end 的limit设置
 */
- (instancetype)limitWithStart:(int)start End:(int)end
{
    self.limit = [NSString stringWithFormat:@"%d,%d",start,end];
    return self;
}

#pragma mark - data 设置数据方法
/**
 *  从字典添加更新的数据源
 */
- (instancetype)dataWithDict:(NSDictionary *)dict
{
    _data = [self filterWithDict:dict];
    return self;
}
/**
 *  从数组添加更新的数据源
 */
- (instancetype)dataWithArray:(NSArray *)arr
{
    __block NSMutableArray *dataArr = [NSMutableArray array];
    if ([arr count]>0) {
        [arr enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop) {
            if ([obj isKindOfClass:[NSDictionary class]]) {
                NSDictionary *dict = [self filterWithDict:obj];
                if([dict count] > 0){
                    [dataArr addObject:dict];
                }
            }
        }];
    }
    _data = [NSArray arrayWithArray:dataArr];
    return self;
}
/**
 *  从JSON添加更新的数据源
 */
-(instancetype)datawithJson:(NSData *)json
{
    id jsondata = [NSJSONSerialization JSONObjectWithData:json options:0 error:nil];
    if([jsondata isKindOfClass:[NSDictionary class]]){
        _data = [self dataWithDict:jsondata];
    }
    if ([jsondata isKindOfClass:[NSArray class]]) {
        _data = [self dataWithArray:jsondata];
    }
    return self;
}

#pragma mark - 数据过滤
/**
 *  过滤非数据库表字段数据
 */
- (NSDictionary *)filterWithDict:(NSDictionary *)dict
{
    __block NSMutableDictionary *filterDict = [NSMutableDictionary dictionary];
    __block BOOL isfound = false;
    [[self.currentTableFields allKeys] enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop) {
        if ([dict objectForKey:obj]) {
            [filterDict addEntriesFromDictionary:[NSDictionary dictionaryWithObject:[dict objectForKey:obj] forKey:obj]];
            isfound = true;
        }
    }];
    if (isfound == false) {
        filterDict = [dict mutableCopy];
    }
    return filterDict;
}

/**
 *  统计查询结果集合数量
 */
- (NSNumber *)queryCount
{
    [self fieldsWithString:@"COUNT(*) as tmp"];
    [self setLimit:@"1"];
    NSArray *result =  [self queryAll];
    return [[[result lastObject] allValues] lastObject];
}

#pragma mark - 设置状态
/**
 *  清除设置的状态
 */
- (instancetype)clean
{
    _fields = nil;
    self.where = nil;
    self.limit = nil;
    _data = nil;
    // _prefix = nil;
    self.order = nil;
    self.group = nil;
    self.cacheID = nil;
    _lastSql = nil;
    _currentTableName = nil;
    return self;
}
/**
 *  锁定上次设置的状态
 */
- (instancetype)lock
{
    self.fields_lock = _fields;
    self.where_lock = self.where;
    self.limit_lock = self.limit;
    self.data_lock = _data;
    //_prefix_lock = _prefix;
    _order_lock = self.order;
    _group_lock = self.group;
    _cacheID_lock = self.cacheID;
    _currentTableFields_lock = _currentTableFields;
    _currentTableName_lock = _currentTableName;
    _lastSql_lock = _lastSql;
    return self;
}
/**
 *  重置到上一次的状态
 */
- (instancetype)reset
{
    _fields = _fields_lock;
    self.where =  _where_lock;
    self.limit= _limit_lock;
    _data=_data_lock;
    //  _prefix = _prefix_lock;
    self.order =_order_lock;
    self.group =_group_lock;
    self.cacheID = _cacheID_lock;
    _currentTableFields = _currentTableFields_lock;
    _currentTableName = _currentTableName_lock;
    _lastSql = _lastSql_lock;
    return self;
}
#pragma mark - cache 缓存处理方法
/**
 *  将数据缓存入文件
 */
- (BOOL)setObject:(id)data ForCacheKey:(NSString *)cacheKey
{
    //记录当前状态，在cache操作后还原
    [[[self lock] clean] setPrefix:@""];
    //选择数据表
    [self setCurrentTableName:@"_cache"];
    //设置查询条件
    [self whereWithDict:@{@"cacheID":cacheKey}];
    //判断是否存在，存在记录则抹掉记录并且重写
    if ([self queryCount]>0) {
        //删除记录
        [self delete];
    }
    NSData *cacheData = [data isKindOfClass:[NSData class]] ? data:[NSKeyedArchiver archivedDataWithRootObject:data];
    
    //添加数据源
    [self dataWithDict:@{@"cacheID":cacheKey,@"cacheData":cacheData}];
    return  [self insert] && [self reset];
}
/**
 *  根据缓存键名读取缓存内容
 */
- (id)objectForCacheKey:(NSString *)cacheKey
{
    //记录当前状态，在cache操作后还原
    [[self lock] clean];
    //选择数据表
    [self setCurrentTableName:@"_cache"];
    //设置查询条件
    [self whereWithDict:@{@"cacheID":cacheKey}];
    //判断是否存在，存在记录则读取缓存，不存在则返回nil
    if ([self queryCount]==0) {
        return nil;
    }
    //读取缓存内容，并根据相应的格式返回缓存的数据格式
    return [NSKeyedUnarchiver unarchiveObjectWithData:[self queryField:@"cacheData"]];
}
#pragma mark - 数据表是否为空
- (BOOL)isEmptyWithTableName:(NSString *)tableName
{
    //重置查询条件
    [self clean];
    //选择要操作的表名
    [self setCurrentTableName:tableName];
    return [[self querySigleInfo] count]==0;
}

#pragma mark - 为字段创建普通索引
- (bool)createIndexWithField:(NSString *)field andTableName:(NSString *)tableName
{
    __block NSMutableString *sql = [NSMutableString stringWithFormat:@"CREATE INDEX IF NOT EXISTS %@ ON %@%@(%@)",field,_prefix,tableName,field];
    _lastSql = sql;
    return [self excuteUpdateWithSql:sql];
}

#pragma mark - getter setter
- (void)setCurrentTableName:(NSString *)currentTableName
{
    _currentTableName = [NSString stringWithFormat:@"%@%@",self.prefix,currentTableName];
    __block NSString *sql = [NSString stringWithFormat:@"PRAGMA table_info(%@)",_currentTableName];
    __block NSMutableDictionary *dict = [NSMutableDictionary dictionary];
    [_queue inDatabase:^(FMDatabase *db) {
        FMResultSet *rs= [db executeQuery:sql];
        while (rs.next) {
            [dict addEntriesFromDictionary: [NSDictionary dictionaryWithObject:[rs stringForColumn:@"type"] forKey:[rs stringForColumn:@"name"]]];
        }
    }];
    _currentTableFields = [NSDictionary dictionaryWithDictionary:dict];
    _lastSql = sql;
}


/**
 *  设置连接的数据库名
 */
- (void)setDbName:(NSString *)dbName
{
    _dbName = dbName;
    _dbPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:_dbName];
    _queue = [FMDatabaseQueue databaseQueueWithPath:_dbPath];
}

#pragma mark - get方法重写
- (NSArray *)fields
{
    if ([_fields count]==0) {
        _fields = [_currentTableFields allKeys];
    }
    return _fields;
}

- (NSString *)prefix
{
    if (_prefix.length==0) {
        return [NSString string];
    }
    return _prefix;
}

- (NSString *)limit
{
    if (_limit.length==0) {
        return [NSString string];
    }
    return [NSString stringWithFormat:@"LIMIT %@",_limit];
}

- (NSString *)where
{
    if (_where.length>0) {
        return [NSString stringWithFormat:@" WHERE %@",[[NSMutableString stringWithString:_where] stringByReplacingOccurrencesOfString:@" WHERE " withString:@""]];
    }
    return [NSString string];
}

- (NSString *)order
{
    if(_order.length>0){
        return [NSString stringWithFormat:@" ORDER BY %@",_order];
    }
    return [NSString string];
}

- (NSString *)group
{
    if (_group.length >0) {
        return [NSString stringWithFormat:@" GROUP BY %@",_group];
    }
    return [NSString string];
}

- (NSArray *)fieldsArray
{
    return _fields;
}

- (NSString *)fieldsString
{
    __block NSMutableString *str = [NSMutableString string];
    [self.fields enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop) {
        str = [NSMutableString stringWithFormat:@"%@,%@",str,obj];
    }];
    NSString *ref = [str stringByTrimmingCharactersInSet:[NSCharacterSet characterSetWithCharactersInString:@","]];
    if ([ref isEqualToString:@""] || [ref isEqual:nil]) {
        return @" * ";
    }
    return ref;
}

- (NSString *)cacheID
{
    if (!_cacheID) {
        return  [NSString stringWithFormat:@"%d" ,(int)[[NSDate date] timeIntervalSince1970]];
    }
    return _cacheID;
}

@end
